home *** CD-ROM | disk | FTP | other *** search
/ Programmer Plus 2007 / Programmer-Plus-2007.iso / Programming / SQL and Data Base / Logic Works ERwin 3.5 / data.1 / erwrelin.six < prev    next >
Encoding:
Text File  |  1998-01-28  |  7.7 KB  |  212 lines
  1. /* ------------------------------------------------------------------------
  2. --      ERWRELIN.SIX                                                                                                             --
  3. --      Copyright (C) Logic Works, Inc. 1994                                                             --
  4. --                                                                                                                                       --
  5. --  Stored function to insert ERwin relationships into the                               --
  6. --  Designer 2000 dictionary                                                                                     --
  7. ------------------------------------------------------------------------- */
  8. CREATE OR REPLACE FUNCTION ERWRELIN(pEntFrom    CI_ENTITIES.NAME%TYPE,
  9.                             pEntTo      CI_ENTITIES.NAME%TYPE,
  10.                             pEntFromID      CI_ENTITIES.ID%TYPE,
  11.                             pEntToID        CI_ENTITIES.ID%TYPE,
  12.                             pOptional       VARCHAR2,
  13.                             pName1          CI_RELATIONSHIP_ENDS.NAME%TYPE,
  14.                             pName2          CI_RELATIONSHIP_ENDS.NAME%TYPE,
  15.                             pApp            CI_APPLICATION_SYSTEMS.NAME%TYPE,
  16.                             pVersion        CI_APPLICATION_SYSTEMS.VERSION%TYPE,
  17.                             pType           SMALLINT,
  18.                             pCardinality CI_RELATIONSHIP_ENDS.MAXIMUM_CARDINALITY%TYPE)
  19.         RETURN CI_RELATIONSHIP_ENDS.ID%TYPE
  20.         AS
  21.             TYPE recEntity IS RECORD
  22.                 (APPLICATION_SYSTEM_OWNED_BY   CI_ENTITIES.APPLICATION_SYSTEM_OWNED_BY%TYPE,
  23.                  ID CI_ENTITIES.ID%TYPE,
  24.                  NAME CI_ENTITIES.NAME%TYPE);
  25.             TYPE recRelend IS RECORD
  26.                 (FROM_ENTITY_REFERENCE  CI_RELATIONSHIP_ENDS.FROM_ENTITY_REFERENCE%TYPE,
  27.                  ID CI_RELATIONSHIP_ENDS.ID%TYPE,
  28.                  NAME CI_RELATIONSHIP_ENDS.NAME%TYPE,
  29.                  TO_ENTITY_REFERENCE   CI_RELATIONSHIP_ENDS.TO_ENTITY_REFERENCE%TYPE);
  30.             prRelend                recRelend;
  31.             stRelend                ciorelationship_end.data;
  32.             stEntity                cioentity.data;
  33.             prEntity                recEntity;
  34.             ToEntityRef             CI_ENTITIES.ID%TYPE;
  35.             FromEntityRef   CI_ENTITIES.ID%TYPE;
  36.             RelEnd1                 CI_RELATIONSHIP_ENDS.ID%TYPE;
  37.             RelEnd2                 CI_RELATIONSHIP_ENDS.ID%TYPE;
  38.             EndInsFail              EXCEPTION;
  39.             vMaxCard                CI_RELATIONSHIP_ENDS.MAXIMUM_CARDINALITY%TYPE;
  40.             vMinCard                CI_RELATIONSHIP_ENDS.MINIMUM_CARDINALITY%TYPE;
  41.             act_status              varchar2(100);
  42.             act_warnings            varchar2(100);
  43. BEGIN
  44.     if cdapi.initialized = false then
  45.         RETURN -1;
  46.     end if;
  47.             BEGIN
  48.                 IF pVersion = 0 THEN 
  49.                     SELECT APPLICATION_SYSTEM_OWNED_BY, ID, NAME INTO prEntity
  50.                         FROM CI_ENTITIES
  51.                         WHERE   CI_ENTITIES.NAME=UPPER(pEntFrom) 
  52.                         AND CI_ENTITIES.APPLICATION_SYSTEM_OWNED_BY = 
  53.                         (SELECT ID FROM CI_APPLICATION_SYSTEMS WHERE 
  54.                         CI_APPLICATION_SYSTEMS.NAME = UPPER(pApp) AND
  55.                         CI_APPLICATION_SYSTEMS.LATEST_VERSION_FLAG='Y');
  56.                 ELSE
  57.                     SELECT APPLICATION_SYSTEM_OWNED_BY, ID, NAME INTO prEntity
  58.                         FROM CI_ENTITIES
  59.                         WHERE   CI_ENTITIES.NAME=UPPER(pEntFrom) 
  60.                         AND CI_ENTITIES.APPLICATION_SYSTEM_OWNED_BY = 
  61.                         (SELECT ID FROM CI_APPLICATION_SYSTEMS WHERE 
  62.                         CI_APPLICATION_SYSTEMS.NAME = UPPER(pApp) AND
  63.                         CI_APPLICATION_SYSTEMS.VERSION=pVersion);
  64.                 END IF;
  65.                 dbms_output.put_line('In relationship: From entity found '||prEntity.ID);
  66.                 FromEntityRef := prEntity.ID;
  67.             EXCEPTION
  68.                 WHEN OTHERS THEN
  69.                     IF pEntFromID = 0 THEN
  70.                         RETURN  -1;
  71.                     ELSE
  72.                         FromEntityRef := pEntFromID;
  73.                     END IF;
  74.             END;
  75.             BEGIN
  76.                 IF pVersion = 0 THEN 
  77.                     SELECT APPLICATION_SYSTEM_OWNED_BY, ID, NAME INTO prEntity
  78.                         FROM CI_ENTITIES
  79.                         WHERE   CI_ENTITIES.NAME=UPPER(pEntTo) 
  80.                         AND CI_ENTITIES.APPLICATION_SYSTEM_OWNED_BY = 
  81.                         (SELECT ID FROM CI_APPLICATION_SYSTEMS WHERE 
  82.                         CI_APPLICATION_SYSTEMS.NAME = UPPER(pApp) AND
  83.                         CI_APPLICATION_SYSTEMS.LATEST_VERSION_FLAG='Y');
  84.                 ELSE
  85.                     SELECT APPLICATION_SYSTEM_OWNED_BY, ID, NAME INTO prEntity
  86.                         FROM CI_ENTITIES
  87.                         WHERE   CI_ENTITIES.NAME=UPPER(pEntTo) 
  88.                         AND CI_ENTITIES.APPLICATION_SYSTEM_OWNED_BY = 
  89.                         (SELECT ID FROM CI_APPLICATION_SYSTEMS WHERE 
  90.                         CI_APPLICATION_SYSTEMS.NAME = UPPER(pApp) AND
  91.                         CI_APPLICATION_SYSTEMS.VERSION=pVersion);
  92.                 END IF;
  93.                 dbms_output.put_line('In relationship: To entity found '||prEntity.ID);
  94.                 ToEntityRef := prEntity.ID;
  95.             EXCEPTION
  96.                 WHEN OTHERS THEN
  97.                     IF pEntToID = 0 THEN
  98.                         RETURN -1;
  99.                     ELSE
  100.                         ToEntityRef :=  pEntToID;
  101.                     END IF;
  102.             END;
  103.             IF pType = 9 THEN
  104.                 BEGIN
  105.                     stEntity.v.NAME := pEntTo;
  106.                     stEntity.i.NAME := true;
  107.                     stEntity.v.SUPERTYPE_REFERENCE := FromEntityRef;
  108.                     stEntity.i.SUPERTYPE_REFERENCE := true;
  109.                     cdapi.open_activity;
  110.                     cioentity.upd(ToEntityRef,stEntity);
  111.                     cdapi.validate_activity(act_status, act_warnings);
  112.                     cdapi.close_activity(act_status);
  113.                     if act_status != 'Y' then
  114.                         cdapi.abort_activity;
  115.                         RETURN -1;
  116.                     else
  117.                         RETURN ToEntityRef;
  118.                     end if;
  119.                 EXCEPTION
  120.                     WHEN OTHERS THEN
  121.                         if cdapi.stacksize > 0 then 
  122.                             if cdapi.activity is not null then 
  123.                                 cdapi.abort_activity;
  124.                             end if;
  125.                         else
  126.                             if cdapi.activity is not null then 
  127.                                 cdapi.abort_activity;
  128.                             end if;
  129.                         end if;
  130.                         RETURN  -1;
  131.                 END;
  132.             ELSE
  133.                 SELECT R1.FROM_ENTITY_REFERENCE,R1.ID,
  134.                     R1.NAME, R1.TO_ENTITY_REFERENCE
  135.                     INTO prRelend
  136.                     FROM CI_RELATIONSHIP_ENDS R1, 
  137.                     CI_RELATIONSHIP_ENDS R2
  138.                     WHERE R1.FROM_ENTITY_REFERENCE=FromEntityRef
  139.                     AND R1.TO_ENTITY_REFERENCE=ToEntityRef
  140.                     AND R1.NAME=pName1 AND
  141.                     R1.OTHER_RELATIONSHIP_END_REF=R2.ID AND
  142.                     R2.FROM_ENTITY_REFERENCE=ToEntityRef AND
  143.                     R2.TO_ENTITY_REFERENCE=FromEntityRef;
  144.                 dbms_output.put_line('Relationship end found'); 
  145.                 RETURN prRelend.ID;
  146.             END IF;                                         
  147.         EXCEPTION
  148.             WHEN NO_DATA_FOUND THEN
  149.                BEGIN
  150.                    dbms_output.put_line('Relationship ends being inserted');    
  151.                    cdapi.open_activity; 
  152.                    if pCardinality = - 1 THEN
  153.                         vMaxCard := 1;
  154.                    else
  155.                         vMaxCard := 0;
  156.                    end if;      
  157.                    IF pCardinality <> -2 THEN
  158.                         vMinCard := 0;
  159.                    ELSE
  160.                         vMinCard := 1;
  161.                    END IF;
  162.                    RelEnd1 := ERWRENIN(FromEntityRef,pName1,vMaxCard,
  163.                                     vMinCard,-1);
  164.                    IF RelEnd1 = -1      THEN
  165.                         raise EndInsFail;
  166.                    END IF;
  167.                    IF pOptional = 'Y'  THEN
  168.                         vMinCard := 0;
  169.                    ELSE
  170.                         vMinCard := 1;
  171.                    END IF;
  172.                    IF pType = 2 OR pType = 7 THEN
  173.                        vMaxCard := 1;                                       
  174.                    ELSE
  175.                        vMaxCard := 0;
  176.                    END IF;      
  177.                    RelEnd2 := ERWRENIN(ToEntityRef,pName2,vMaxCard,
  178.                                     vMinCard,RelEnd1);
  179.                    IF RelEnd2 = -1 THEN
  180.                         raise EndInsFail;
  181.                    END IF;
  182.                    cdapi.validate_activity(act_status, act_warnings);
  183.                    cdapi.close_activity(act_status);
  184.                    if act_status != 'Y' then
  185.                         cdapi.abort_activity;
  186.                         RETURN -1;
  187.                    else
  188.                         RETURN RelEnd2;
  189.                    end if;
  190.                EXCEPTION
  191.                    WHEN EndInsFail THEN
  192.                         if cdapi.activity is not null then 
  193.                             cdapi.abort_activity;
  194.                         end if;
  195.                         RETURN -1;
  196.                    WHEN OTHERS THEN
  197.                         if cdapi.stacksize > 0 then 
  198.                             if cdapi.activity is not null then 
  199.                                 cdapi.abort_activity;
  200.                             end if;
  201.                         else
  202.                             if cdapi.activity is not null then 
  203.                                 cdapi.abort_activity;
  204.                             end if;
  205.                         end if;
  206.                         RETURN  -1;
  207.                END;
  208.             WHEN OTHERS THEN
  209.                 RETURN -1;                                                                                                                                                                                 
  210. END ERWRELIN;
  211. /
  212.